﻿CREATE TABLE [dbo].[Buget] (
    [IDBuget]      INT             IDENTITY (1, 1) NOT NULL,
    [Denumire]     VARCHAR (50)    CONSTRAINT [DF_Buget_Denumire] DEFAULT ('') NOT NULL,
    [DataInceput]  DATETIME        CONSTRAINT [DF_Buget_DataInceput] DEFAULT (getdate()) NOT NULL,
    [DataSfarsit]  DATETIME        CONSTRAINT [DF_Buget_DataSfarsit] DEFAULT (getdate()) NOT NULL,
    [MultiAnual]   BIT             CONSTRAINT [DF_Buget_MultiAnual] DEFAULT ((0)) NOT NULL,
    [Rectificativ] BIT             CONSTRAINT [DF_Buget_Rectificativ] DEFAULT ((0)) NOT NULL,
    [Suma]         DECIMAL (18, 2) NOT NULL,
    [IDJurnal]     INT             CONSTRAINT [DF_Buget_IDJurnal] DEFAULT ((0)) NOT NULL,
    [ArtAl]        VARCHAR (40)    CONSTRAINT [DF_Buget_ArtAl] DEFAULT ('') NOT NULL,
    [CodUnitate]   SMALLINT        CONSTRAINT [DF_Buget_CodUnitate] DEFAULT ((0)) NOT NULL,
    [CodFormula]   BIGINT          NULL,
    CONSTRAINT [PK_Buget] PRIMARY KEY CLUSTERED ([IDBuget] ASC),
    CONSTRAINT [FK_Buget_ArticoleAliniate] FOREIGN KEY ([ArtAl], [CodUnitate]) REFERENCES [dbo].[ArticoleAliniate] ([ArtAl], [CodUnitate]),
    CONSTRAINT [FK_Buget_Jurnale] FOREIGN KEY ([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
    CONSTRAINT [FK_Buget_Unitati] FOREIGN KEY ([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
);


GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[Update_Buget] 
   ON  [dbo].[Buget]
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    --insereaza in centralizator
	IF NOT EXISTS(SELECT * FROM BugetCentralizator a JOIN inserted i ON a.CodUnitate=i.CodUnitate
			 AND a.IDJurnal=i.IDjurnal AND a.ArtAl=i.ArtAl AND a.Data=i.DataInceput)
	BEGIN
		INSERT INTO [BugetCentralizator](Data,IDJurnal,ArtAl, CodUnitate,Suma,ArtAlParinte)
		SELECT CAST(CONVERT(varchar(10),i.DataInceput,101) AS Datetime),i.IDJurnal, a.ArtAl,i.CodUnitate,0,a.AAParinte FROM inserted i JOIN 
			[articoleAliniate] a ON a.CodUnitate =i.CodUnitate
	END
    --- updateaza articolul corespunzator
	UPDATE [BugetCentralizator]
	SET Suma = t2.Suma
	FROM 
	(select a.CodUnitate,a.IDJurnal AS IDJurnal ,a.ArtAl,CAST(CONVERT(varchar(10),a.DataInceput,101) AS Datetime) AS DataInceput,sum(a.suma) AS Suma FROM Buget a
		JOIN inserted i ON a.CodUnitate=i.CodUnitate AND a.IDJurnal=i.IDJurnal AND a.ArtAl=i.ArtAl AND CAST(CONVERT(varchar(10),a.DataInceput,101) AS Datetime) = 
		CAST(CONVERT(varchar(10),i.DataInceput,101) AS Datetime)
		GROUP BY a.CodUnitate,a.IDJurnal,a.ArtAl,CAST(CONVERT(varchar(10),a.DataInceput,101) AS Datetime)) t2
	WHERE  [BugetCentralizator].Data = t2.DataInceput AND [BugetCentralizator].CodUnitate=t2.CodUnitate AND 
		[BugetCentralizator].IDJurnal = t2.IDJurnal AND [BugetCentralizator].ArtAl=t2.ArtAl
	--actualizeaza parintii
    DECLARE CursInsert CURSOR 
	FOR SELECT ArtAl,IDjurnal,CodUnitate,DataInceput  FROM inserted
	DECLARE @ArtAl varchar(40), @parinte varchar(40)
	DECLARE @IDJurnal int
	DECLARE @CodUnitate smallint
	DECLARE @Data datetime	
	OPEN CursInsert
	FETCH NEXT FROM CursInsert INTO @ArtAl,@IDJurnal,@CodUnitate,@Data
	while @@FETCH_STATUS = 0
	BEGIN
		SELECT @parinte = [ArtAlParinte] FROM [BugetCentralizator] WHERE [ArtAl]=@ArtAl
			AND IDJurnal = @IDJurnal
			AND CodUnitate = @CodUnitate
			AND Data = @Data
		WHILE 1=1
		BEGIN
			UPDATE [BugetCentralizator] SET
				Suma = (SELECT SUM(Suma) FROM [BugetCentralizator] b WHERE ArtAlParinte=@parinte AND 
							b.CodUnitate = [BugetCentralizator].CodUnitate AND 
							b.IDJurnal = [BugetCentralizator].IDJurnal AND
							b.Data = [BugetCentralizator].Data AND ArtAl<>'N/A')
			WHERE ArtAl=@parinte AND 
				  CodUnitate = @CodUnitate AND 
				  IDJurnal = @IDJurnal AND
				  Data = @Data
			
			if @parinte='N/A'
				BREAK
			SELECT @parinte = [ArtAlParinte] FROM [BugetCentralizator] WHERE [ArtAl]=@parinte
				AND IDJurnal = @IDJurnal
				AND CodUnitate = @CodUnitate
				AND Data = @Data

		END
		FETCH NEXT FROM CursInsert INTO @ArtAl,@IDJurnal,@CodUnitate,@Data
	END
	CLOSE CursInsert
	DEALLOCATE CursInsert
	


END